![]() |
Java Database Programming with JDBC
by Pratik Patel Coriolis, The Coriolis Group ISBN: 1576100561 Pub Date: 10/01/96 |
Previous | Table of Contents | Next |
Mastering SQL querying is not an easy task, but with the proper mind set, it is intuitive and efficient, thanks to the relational model upon which SQL is based.
The syntax of the SELECT statement is shown here:
SELECT column_names FROM table_names WHERE predicates
Lets take a look at the various functions of the SELECT command. To retrieve a complete table, run this query:
SELECT * FROM EMPLOYEE;
To get a list of employees in the Editorial department, run this query:
SELECT * FROM EMPLOYEE WHERE department = 'Editorial';
To sort the list based on the employees last names, use the ORDER BY directive:
SELECT * FROM EMPLOYEE WHERE department= 'Editorial' ORDER BY lastname;
To get this ordered list but only see the employee number, enter the following statements:
SELECT empno FROM EMPLOYEE WHERE department = 'Editorial' ORDER BY lastname;
To get a list of users with the name Pratik Patel, you would enter:
SELECT * FROM EMPLOYEE WHERE (firstname='Pratik') AND (lastname='Patel');
What if we want to show two tables at once? No problem, as shown here:
SELECT EMPLOYEE.*, CONFIDENTIAL.* FROM EMPLOYEE, CONFIDENTIAL;
Heres a more challenging query: Show the salary for employees in the Editorial department. According to our tables, the salary information is in the CONFIDENTIAL table, and the department in which an employee belongs is in the EMPLOYEE table. How do we associate a comparison in one table to another? Since we used the reference of the employee number in the CONFIDENTIAL table from the EMPLOYEE table, we can specify the employees that match a specified department, and then use the resulting employee number to retrieve the salary information from the CONFIDENTIAL table:
SELECT c.salary FROM EMPLOYEE as e, CONFIDENTIAL as c WHERE e.department = 'Editorial' AND c.empno = e.empno;
We have declared something like a variable using the as keyword. We can now reference the specific fields in the table using a ., just like an object. Lets begin by determining which people in the entire company are making more than $25,000:
SELECT salary FROM CONFIDENTIAL WHERE salary > 25000;
Now lets see who in the Editorial department is making more than $25,000:
SELECT c.salary FROM EMPLOYEE as e, CONFIDENTIAL as c WHERE e.department = 'Editorial' AND c.empno = e.empno AND c.salary > 25000;
You can perform a number of other functions in SQL, including averages. Heres how to get the average salary of the people in the Editorial department:
SELECT AVG (c.salary) FROM EMPLOYEE as e, CONFIDENTIAL as c WHERE e.department = 'Editorial' AND c.empno = e.empno;
Of course, the possibilities with SQL exceed the relatively few examples shown in this chapter. Because this books goal is to introduce the JDBC specifically, I didnt use complex queries in the examples. And now our discussion on SQL is complete. If you are interested in learning more about SQL, I recommend that you check out our books Website, where I have posted a list of recommended books on the topic of SQL and distributed databases.
The next chapter begins our journey into JDBC. Ill show you how to use JDBC drivers for connecting to data sources. Then well cover installing drivers, as well as the proper way to use drivers that are dynamically fetched with an applet. Finally, well discuss the security restrictions of using directly downloaded drivers as opposed to locally installed drivers.
Previous | Table of Contents | Next |